package com.palm.core.util;


import ch.rabanti.nanoxlsx4j.Cell;
import ch.rabanti.nanoxlsx4j.Workbook;
import ch.rabanti.nanoxlsx4j.Worksheet;
import com.alibaba.fastjson.annotation.JSONField;
import com.palm.core.anno.Excel;
import com.palm.core.data.Item;
import com.palm.core.data.Triple;
import com.palm.core.translator.FormatTranslator;
import com.palm.core.translator.MultipleTranslator;
import com.palm.core.translator.SimpleTranslator;
import com.palm.core.translator.Translator;
import lombok.SneakyThrows;
import org.noear.solon.core.Aop;
import org.noear.solon.core.handle.Context;
import org.noear.solon.extend.sqltoy.DbManager;
import org.sagacity.sqltoy.SqlToyContext;
import org.sagacity.sqltoy.config.annotation.Translate;
import org.sagacity.sqltoy.dao.SqlToyLazyDao;
import org.sagacity.sqltoy.utils.BeanUtil;

import javax.sql.DataSource;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.Format;
import java.util.*;
import java.util.function.BiConsumer;


/**
 * 数据导入导出工具
 * 先做excel，考虑下DBF
 */
public class TransportUtil {
    private static SqlToyContext sqlToyContext;

    static {
        Aop.getAsyn(SqlToyContext.class, bw -> sqlToyContext = bw.raw());
    }

    /**
     * 简单的按字段导出
     *
     * @param dataList
     * @param translators
     * @param outputStream
     */
    public static void exportExcel(List dataList, Map<String, Translator> translators, OutputStream outputStream) {
        if (dataList == null || dataList.size() == 0 || dataList.get(0) == null) {
            return;
        }
        Class exportDef = dataList.get(0).getClass();
        List<Item> titles = new ArrayList<>();
        int order = 0;
        for (Field field : exportDef.getDeclaredFields()) {
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            title.setOrder(order++);
            titles.add(title);
        }
        Collections.sort(titles);
        Workbook workbook = new Workbook("Sheet1");
        Worksheet ws = workbook.getCurrentWorksheet();
        for (Item title : titles) {
            ws.addNextCell(title.getTitle());
        }
        boolean isMap = false;
        try (OutputStream out = outputStream) {
            for (Object data : dataList) {
                ws.goToNextRow();
                for (Item title : titles) {
                    Object value = null;
                    if (isMap || data instanceof Map) {
                        isMap = true;
                        value = ((Map) data).get(title.getKey());
                    } else {
                        try {
                            value = BeanUtil.getProperty(data, title.getKey());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (value == null) {
                        int c = ws.getCurrentColumnNumber();
                        ws.setCurrentColumnNumber(c + 1);
                    } else {
                        Translator translator = translators.get(title.getKey());
                        if (translator != null) {
                            ws.addNextCell(translator.translate(data, title.getKey(), value));
                        } else {
                            ws.addNextCell(value);
                        }
                    }
                }
            }
            workbook.saveAsStream(out);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ch.rabanti.nanoxlsx4j.exceptions.IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel到输出流
     *
     * @param dao
     * @param sqlId
     * @param bean         查询参数及导出模板
     * @param outputStream 输出流
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, OutputStream outputStream) {
        exportExcel(dao, sqlId, bean, new HashMap<>(), outputStream);
    }

    /**
     * @param dataList     需要导出的数据
     * @param exportDef    导出数据格式定义
     * @param translators  翻译器,key对应字段名
     * @param outputStream 导出到输出流
     */
    public static void exportExcel(List dataList, Class exportDef, Map<String, Translator> translators, OutputStream outputStream) {
        //获得导出字段及表头
        List<Item> titles = new ArrayList<>();
        Set<String> fields = new HashSet<>();
        for (Field field : exportDef.getDeclaredFields()) {
            Excel export = field.getAnnotation(Excel.class);
            if (export == null) {
                continue;
            }
            if (!export.out()) {
                fields.add(field.getName());
                continue;
            }
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            if (StringUtil.isNotEmpty(export.value())) {
                title.setTitle(export.value());
            }
            title.setOrder(export.order());
            titles.add(title);
            fields.add(field.getName());
            //使用本身translate,不再额外处理,只处format理格式转换
            if (!translators.containsKey(title.getKey())) {
                Translator translator = null;
                JSONField jf = field.getAnnotation(JSONField.class);
                if (jf != null && StringUtil.isNotEmpty(jf.format())) {
                    translator = new FormatTranslator(jf.format());
                    translators.put(title.getKey(), translator);
                }
//                if (StringUtil.isNotEmpty(export.translate())) {
//                    translator = getTranslator(export.translate(), export.translateType(), export.multiTranslate());
//                } else if (StringUtil.isNotEmpty(export.format())) {
//                    translator = new FormatTranslator(export.format());
//                }
//                if (translator != Translator.DEFAULT) {
//                    translators.put(title.getKey(), translator);
//                }
            }
        }
        //父级,最多处理两级
        for (Field field : exportDef.getSuperclass().getDeclaredFields()) {
            Excel export = field.getAnnotation(Excel.class);
            if (export == null) {
                continue;
            }
            if (!export.out()) {
                continue;
            }
            if (fields.contains(field.getName())) {
                continue;
            }
            Item title = new Item();
            title.setKey(field.getName());
            title.setTitle(field.getName());
            if (StringUtil.isNotEmpty(export.value())) {
                title.setTitle(export.value());
            }
            title.setOrder(export.order());
            titles.add(title);
            if (!translators.containsKey(title.getKey())) {
                Translator translator = null;
                JSONField jf = field.getAnnotation(JSONField.class);
                if (jf != null && StringUtil.isNotEmpty(jf.format())) {
                    translator = new FormatTranslator(jf.format());
                    translators.put(title.getKey(), translator);
                }
            }
        }
        Collections.sort(titles);
        Workbook workbook = new Workbook("Sheet1");
        Worksheet ws = workbook.getCurrentWorksheet();
        for (Item title : titles) {
            ws.addNextCell(title.getTitle());
        }
        Boolean isMap = null;
        try (OutputStream out = outputStream) {
            for (Object data : dataList) {
                ws.goToNextRow();
                for (Item title : titles) {
                    Object value = null;
                    if (isMap == null) {
                        isMap = data instanceof Map;
                    }
                    if (isMap) {
                        value = ((Map) data).get(title.getKey());
                    } else {
                        try {
                            value = BeanUtil.getProperty(data, title.getKey());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (value == null) {
                        int c = ws.getCurrentColumnNumber();
                        ws.setCurrentColumnNumber(c + 1);
                    } else {
                        Translator translator = translators.get(title.getKey());
                        if (translator != null) {
                            ws.addNextCell(translator.translate(data, title.getKey(), value));
                        } else {
                            ws.addNextCell(value);
                        }
                    }
                }
            }
            workbook.saveAsStream(out);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ch.rabanti.nanoxlsx4j.exceptions.IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel到输出流
     *
     * @param dao
     * @param sqlId
     * @param bean         查询参数及导出模板
     * @param translators  转翻器,用于自定义输出字段的值，可以是字段转换，格式话等，map的key值为bean中需要转换的字段名
     * @param outputStream 输出流
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, Map<String, Translator> translators, OutputStream outputStream) {
//        QueryExecutor qe = new QueryExecutor(sqlId, bean);
//        qe.resultType(Map.class);
        List dataList = dao.findBySql(sqlId, bean);
        exportExcel(dataList, bean.getClass(), translators, outputStream);
    }

    /**
     * @param dao
     * @param sqlId
     * @param bean     查询参数及导出模板
     * @param fileName 导出文件名
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, String fileName) {
        exportExcel(dao, sqlId, bean, new HashMap<>(), fileName);
    }

    /**
     * @param dao
     * @param sqlId
     * @param bean     查询参数及导出模板
     * @param fileName 导出文件名
     */
    public static void exportExcel(SqlToyLazyDao dao, String sqlId, Serializable bean, Map<String, Translator> translators, String fileName) {
        try {
            Context ctx = Context.current();
            ctx.setHandled(true);
            ctx.contentType("Content-Type:application/vnd.ms-excel");
            ctx.headerSet("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx;");
            exportExcel(dao, sqlId, bean, translators, ctx.outputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static <T> List<T> importExcel(InputStream in, Class<T> type) {
        return importExcel(in, type, new HashMap<>());
    }

    /**
     * @param in
     * @param type        需要转换出来的bean类型
     * @param translators 转译器,用于值定义翻译对应的字段,key值为bean的name而非表格中的title
     * @param <T>
     * @return
     */
    @SneakyThrows
    public static <T> List<T> importExcel(InputStream in, Class<T> type, Map<String, Translator> translators) {

        //获得导入字段及表头
        Map<String, Triple<Excel, Field, Integer>> imports = new HashMap<>();
        Set<String> fields = new HashSet<>();
        for (Field field : type.getDeclaredFields()) {
            Excel importField = field.getAnnotation(Excel.class);
            if (importField == null) {
                continue;
            }
            fields.add(field.getName());
            if (!importField.in()) {
                continue;
            }
            String name = importField.value();
            if (StringUtil.isEmpty(name)) {
                name = field.getName();
            }
            imports.put(name, new Triple<>(importField, field));
        }
        for (Field field : type.getSuperclass().getDeclaredFields()) {
            Excel importField = field.getAnnotation(Excel.class);
            if (importField == null) {
                continue;
            }
            if (fields.contains(field.getName())) {
                continue;
            }
            if (!importField.in()) {
                continue;
            }
            String name = importField.value();
            if (StringUtil.isEmpty(name)) {
                name = field.getName();
            }
            imports.put(name, new Triple<>(importField, field));
        }
        if (imports.isEmpty()) {
            throw new RuntimeException(type.getName() + "没有任何字段被@ExcelImport标注");
        }
        Workbook workbook = Workbook.load(in);
        Worksheet ws = workbook.getCurrentWorksheet();
        int lc = ws.getLastColumnNumber();
        int lr = ws.getLastRowNumber();
        //ws.getCell()
        //匹配字段
        int row = 0;
        for (int col = 0; col <= lc; col++) {
            Cell cell = ws.getCell(col, row);
            if (cell == null || cell.getValue() == null) {
                continue;
            }
            String cellName = cell.getValue().toString();
            if (StringUtil.isEmpty(cellName)) {
                continue;
            }
            Triple<Excel, Field, Integer> def = imports.get(cellName);
            if (def != null) {
                def.setTree(col);
            }
        }
        //过滤数据
        Map<Integer, BiConsumer> fieldValueSetters = new HashMap<>();
        for (Triple<Excel, Field, Integer> def : imports.values()) {
            Integer i = def.getTree();
            if (i == null) {
                continue;
            }
            Field field = def.getTow();
            Excel ei = def.getOne();
            Translator translator = translators.get(def.getTow().getName());
            if (translator == null) {
                Translate translate = field.getAnnotation(Translate.class);
                if (translate == null) {
                    translator = Translator.DEFAULT;
                } else {
                    //获得反向翻译
                    Translator inner = getUnTranslator(translate.cacheName(), translate.cacheType(), translate.join());
                    translator = (target, filedName, value) -> {
                        Object t1 = inner.translate(target, filedName, value);
                        try {
                            BeanUtil.setProperty(target, translate.keyField(), t1);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        return value;
                    };
                }

//                if (StringUtil.isNotEmpty(ei.translate())) {
//                    translator = getUnTranslator(ei.translate(), ei.translateType(), ei.multiTranslate());
//                } else if (StringUtil.isNotEmpty(ei.format())) {
//                    // field.getType().
//                    translator = new FormatTranslator(ei.format());
//                } else {
//                    translator = Translator.DEFAULT;
//                }
            }
            Translator finalTranslator = translator;
            final boolean isString = field.getType() == String.class;
            if (isString) {
                JSONField jf = field.getAnnotation(JSONField.class);
                String numFormat = "#.######";
                if (jf != null && StringUtil.isNotEmpty(jf.format())) {
                    numFormat = jf.format();
                }
                final Format simpleFormat = new DecimalFormat(numFormat);
                BiConsumer cs = (obj, value) -> {
                    Object realValue = finalTranslator.translate(obj, field.getName(), value);
                    try {
                        if (realValue instanceof Number) {
                            realValue = simpleFormat.format(realValue);
                        }
                        BeanUtil.setProperty(obj, field.getName(), realValue);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                };
                fieldValueSetters.put(i, cs);
            } else {
                BiConsumer cs = (obj, value) -> {
                    Object realValue = finalTranslator.translate(obj, field.getName(), value);
                    try {
                        BeanUtil.setProperty(obj, field.getName(), realValue);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                };
                fieldValueSetters.put(i, cs);
            }

        }
        List result = new ArrayList();
        //获取数据
        for (row = 1; row <= lr; row++) {
            Object obj = type.newInstance();
            for (Map.Entry<Integer, BiConsumer> entry : fieldValueSetters.entrySet()) {
                entry.getValue().accept(obj, ws.getCell(entry.getKey(), row).getValue());
            }
            result.add(obj);
        }
        return result;
    }

    //

    //反向翻译
    public static Translator getUnTranslator(String name, String type, String split) {
        if (sqlToyContext == null) {
            return Translator.DEFAULT;
        }
        HashMap<String, Object[]> data = sqlToyContext.getTranslateManager().getCacheData(name, StringUtil.isEmpty(type) ? null : type);
        Map<Object, Object> transMap = new HashMap<>();
        for (Object[] dt : data.values()) {
            transMap.put(dt[1], dt[0]);
        }
        return StringUtil.isNotEmpty(split) ? new MultipleTranslator(transMap, split) : new SimpleTranslator(transMap);
    }

    /**
     * 正向翻译
     *
     * @param name
     * @param type
     * @param multi
     * @return
     */
    public static Translator getTranslator(String name, String type, boolean multi) {
        if (sqlToyContext == null) {
            return Translator.DEFAULT;
        }
        HashMap<String, Object[]> data = sqlToyContext.getTranslateManager().getCacheData(name, StringUtil.isEmpty(type) ? null : type);
        Map<Object, Object> transMap = new HashMap<>();
        for (Object[] dt : data.values()) {
            transMap.put(dt[0], dt[1]);
        }
        return multi ? new MultipleTranslator(transMap) : new SimpleTranslator(transMap);
    }
}
